﻿Public Class oCustomer
    Public Function GetAllCustomer(ByVal dataBaseName As String, ByVal companyCode As String, ByVal isAdmin As Boolean) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oCustomer")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                If isAdmin Then
                    query.Append("SELECT TOP 1000 T0.*,T1.CompanyName,T3.GradeName FROM oCustomer T0 JOIN oCompany T1 ON T0.CompanyCode = T1.CompanyCode JOIN oUser T2 ON T0.CreatedUserID = T2.UserID ")
                    query.Append(" LEFT JOIN oCustomerGrade T3 ON T0.GradeID  = T3.GradeID")
                    query.Append("  ORDER BY T0.CustomerID DESC")
                    ds = connect.ObjectGetAll_Query_SAP(query.ToString())
                Else
                    query.Append("SELECT TOP 1000 T0.*,T1.CompanyName,T3.GradeName FROM oCustomer T0 JOIN oCompany T1 ON T0.CompanyCode = T1.CompanyCode JOIN oUser T2 ON T0.CreatedUserID = T2.UserID  ")
                    query.Append(" LEFT JOIN oCustomerGrade T3 ON T0.GradeID  = T3.GradeID")
                    query.Append(" WHERE T0.CompanyCode = @Param1  ")
                    query.Append(" ORDER BY T0.CustomerID DESC")
                    ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {companyCode})
                End If
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetCustomersForSearch(ByVal dataBaseName As String, ByVal companyCode As String, ByVal keySearch As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oCustomer")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                    query.Append("SELECT T0.*,T1.CompanyName,T3.GradeName FROM oCustomer T0 JOIN oCompany T1 ON T0.CompanyCode = T1.CompanyCode JOIN oUser T2 ON T0.CreatedUserID = T2.UserID  ")
                    query.Append(" LEFT JOIN oCustomerGrade T3 ON T0.GradeID  = T3.GradeID")
                query.Append(" WHERE T0.CompanyCode = @Param1 AND (T0.CustomerCode LIKE '%' + @Param2 + '%' OR T0.CustomerName LIKE '%' + @Param2 + '%' )  ")
                    query.Append(" ORDER BY T0.CustomerID DESC")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {companyCode, keySearch})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetCustomerByCode(ByVal dataBaseName As String, ByVal companyCode As String, ByVal customerCode As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oCustomer")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT *,T3.GradeName FROM oCustomer  T0 JOIN oCompany   T1 ON T0.CompanyCode = T1.CompanyCode  JOIN oUser T2 ON T0.CreatedUserID = T2.UserID")
                query.Append(" LEFT JOIN oCustomerGrade T3 ON T0.GradeID  = T3.GradeID")
                query.Append("  WHERE CustomerCode = @Param1 AND T0.CompanyCode  = @Param2")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {customerCode, companyCode})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetCustomerByID(ByVal dataBaseName As String, ByVal customerID As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oCustomer")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT *,T3.GradeName FROM oCustomer  T0 JOIN oCompany   T1 ON T0.CompanyCode = T1.CompanyCode  JOIN oUser T2 ON T0.CreatedUserID = T2.UserID")
                query.Append(" LEFT JOIN oCustomerGrade T3 ON T0.GradeID  = T3.GradeID")
                query.Append("  WHERE CustomerID = @Param1")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {customerID})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function GetCountCustomer(ByVal dataBaseName As String, ByVal companyCode As String) As Long
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oCustomer")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT COUNT(*) AS TotalRow FROM oCustomer")
                query.Append("  WHERE CompanyCode = @Param1")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {companyCode})
                If ds IsNot Nothing AndAlso ds.Tables.Count > 0 AndAlso ds.Tables(0).Rows.Count > 0 Then

                    Dim count As Long = Long.Parse(ds.Tables(0).Rows(0)("TotalRow").ToString())
                    If count > 1000 Then
                        Return 1000
                    Else
                        Return count
                    End If
                End If
            End If
            Return 0
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function UpdateJsonCustomer(ByVal customerId As String, _
                                          ByVal customerCode As String, ByVal customerName As String, ByVal companyCode As String, _
                                          ByVal createdUserId As String, ByVal createdDate As DateTime, ByVal isActive As Boolean, ByVal billingAddress As String, _
                                          ByVal shippingAddress As String, ByVal contactPerson As String, _
                                          ByVal phoneNumber As String, ByVal email As String, ByVal birthDay As DateTime, ByVal specialDay As DateTime, _
                                          ByVal gradeID As String, ByVal databaseName As String, ByVal isInsert As Boolean) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setSQLDB(databaseName)
            If errMsg.Length = 0 Then
                Dim params = New Object() {customerCode, customerName, companyCode, createdUserId, _
                                           isActive, billingAddress, shippingAddress, contactPerson, phoneNumber, email, birthDay, specialDay}
                Dim query As New StringBuilder
                If isInsert = True Then
                    query.Append(" INSERT INTO [oCustomer]")
                    query.Append("  ([CustomerCode]")
                    query.Append(" ,[CustomerName]")
                    query.Append(" ,[CompanyCode]")
                    query.Append(" ,[CreatedUserID]")
                    query.Append(" ,[IsActive]")
                    query.Append(" ,[BillingAddress]")
                    query.Append(" ,[ShippingAddress]")
                    query.Append(" ,[ContactPerson]")
                    query.Append(" ,[PhoneNumber]")
                    query.Append(" ,[Email]")
                    query.Append(" ,[Birthday]")
                    query.Append(" ,[SpecialDay])")
                    query.Append("VALUES (@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,@Param9,@Param10,@Param11,@Param12)")
                    Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), params)
                Else
                    query.Append("UPDATE  [oCustomer] SET ")
                    query.Append("  [CustomerName] = @Param1")
                    query.Append("  ,[CompanyCode] = @Param2")
                    query.Append("  ,[IsActive] = @Param3")
                    query.Append("  ,[BillingAddress] = @Param4")
                    query.Append("  ,[ShippingAddress] = @Param5")
                    query.Append("  ,[ContactPerson] = @Param6")
                    query.Append("  ,[PhoneNumber] = @Param7")
                    query.Append("  ,[Email] = @Param8")
                    query.Append("  ,[Birthday] = @Param9")
                    query.Append("  ,[SpecialDay] = @Param10")
                    query.Append(" WHERE [CustomerID] = @Param11")
                    connect.Object_Execute_SAP(query.ToString(), New Object() {customerName, companyCode,
                                           isActive, billingAddress, shippingAddress, contactPerson, phoneNumber, email, birthDay, specialDay, customerId})
                End If
            Else
                Return errMsg
            End If
        Catch ex As Exception

            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Public Function UpdateCustomer(ByVal data As DataSet, ByVal databaseName As String, ByVal isInsert As Boolean) As String
        Dim connect As New Connection()
        Try
            For Each row As DataRow In data.Tables("oCustomer").Rows
                Dim errMsg As String = connect.setSQLDB(databaseName)
                If errMsg.Length = 0 Then
                    Dim params = New Object() {row("CustomerCode"), row("CustomerName"), row("CreatedUserID"), row("IsActive"), row("BillingAddress"), _
                                              row("ShippingAddress"), row("ContactPerson"), row("PhoneNumber"), row("Email")}
                    Dim query As New StringBuilder
                    If isInsert = True Then
                        query.Append(" INSERT INTO [oCustomer]")
                        query.Append("  ([CustomerCode]")
                        query.Append(" ,[CustomerName]")
                        query.Append(" ,[CompanyCode]")
                        query.Append(" ,[CreatedUserID]")
                        query.Append(" ,[IsActive]")
                        query.Append(" ,[BillingAddress]")
                        query.Append(" ,[ShippingAddress]")
                        query.Append(" ,[ContactPerson]")
                        query.Append(" ,[PhoneNumber]")
                        query.Append(" ,[Email]) ")
                        query.Append("VALUES (@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,@Param9,@Param10)")
                        Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), params)
                    Else
                        query.Append("UPDATE  [oCustomer] SET ")
                        query.Append("  [CustomerName] = @Param1")
                        query.Append("  ,[CompanyCode] = @Param2")
                        query.Append("  ,[CreatedUserID] = @Param3")
                        query.Append("  ,[IsActive] = @Param4")
                        query.Append("  ,[BillingAddress] = @Param5")
                        query.Append("  ,[ShippingAddress] = @Param6")
                        query.Append("  ,[ContactPerson] = @Param7")
                        query.Append("  ,[PhoneNumber] = @Param8")
                        query.Append("  ,[Email] = @Param9")
                        query.Append("  ,[CustomerCode] = @Param10")
                        query.Append(" WHERE [CustomerID] = @Param11")
                        connect.Object_Execute_SAP(query.ToString(), New Object() {row("CustomerName"), row("CreatedUserID"), row("IsActive"), row("BillingAddress"), _
                                              row("ShippingAddress"), row("ContactPerson"), row("PhoneNumber"), row("Email"), row("CustomerCode"), row("CustomerID")})
                    End If
                Else
                    Return errMsg
                End If
            Next
        Catch ex As Exception

            Return ex.Message
        End Try
        Return String.Empty
    End Function
End Class
